# 用于生成mysql8主从复制的配置文件
# 用于生成docker-compose文件

# 两种使用方法
# 第一种
# 在无关电脑上运行，会依据members生成3份配置文件，将3份配置文件拷贝到3台mysql服务器上，然后按提示运行
# 3台自己组成集群
# 第二种
# 将此程序拷贝到3台mysql服务器上，运行时指定自己身份是master1还是slave1或2
# 程序自动配置，按提示运行，3台自己组成集群

import os
import yaml


def runit():
    conffile = "conf.yaml"
    if not os.path.isfile(conffile):
        print("没有找到配置文件,程序将生成一份配置文件模板conf.yaml,请按实际情况修改配置文件后再次运行")
        mk_conf(conffile)
        exit(0)

    global members
    members = get_yaml_data(conffile)

    # 第一种情况下，3份配置文件放在当前outputdir目录下
    outputdir = members['zaiyiqi']['outputroot']
    if not os.path.exists(outputdir):
        os.makedirs(outputdir)

    dorun()

def get_yaml_data(yaml_file):
    file = open(yaml_file, 'r', encoding="utf-8")
    file_data = file.read()
    file.close()
    data = yaml.load(file_data, Loader=yaml.FullLoader)
    return data

def getallipport():
    ipport=""
    for m in members['masters']:
        ipport+=f"{m['ipaddr']}:{m['port']},"
    for s in members['slaves']:
        ipport+=f"{s['ipaddr']}:{s['port']},"
    return ipport[:-1]

def mk_mysql_conf(select="masters",id=0):
    data = members[select][id]
    mysqlconf='''
[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/data/mysql/mysqld.log


[mysqld]
user = root
port = 3306
socket=/var/lib/mysql/mysql.sock
# mysql错误日志存放在系统路径
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names = 1

log-bin = mysql-bin
log-bin-index = binlogs.index
binlog_format = row
binlog_rows_query_log_events = on
binlog_checksum = none

slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4
slave_preserve_commit_order = 1

#GITD
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery = 1

relay_log = /var/lib/mysql/relay.log
relay-log-index = /var/lib/mysql/relay.index
master_info_repository = table
relay_log_info_repository = table

#MGR
#GR配置项 基中loose前缀表示若group Replication plugin未加载 mysql server仍明治维新启动
plugin_load="group_replication=group_replication.so"
transaction_write_set_extraction = XXHASH64 #对每个事务获取write set，并且用XXHASH64算法获取hash值
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #组名，此处可拿select.uuid()生成
loose-group_replication_start_on_boot = off #在mysqld启动时不自动启动组复制
loose-group_replication_bootstrap_group = off #关闭，如果打开会造成脑裂 #是否启动集群，注意，该选项任何时候只能用于一个节点，通常情况下启动集群的时候使用，启动之后需要关闭该选项
loose-group_replication_member_weight = 50 #权重选择

skip-host-cache
skip-name-resolve
skip-external-locking
    #character-set-server=utf8
    #lower_case_table_names=1
event_scheduler=on
log_bin_trust_function_creators=on
    #max_connections = 3000
external-locking = FALSE
    #max_allowed_packet = 32M
    #sort_buffer_size = 8M
    #join_buffer_size = 2M
thread_cache_size = 300
    #query_cache_size = 64M
    #query_cache_limit = 4M
    #read_rnd_buffer_size = 8M
    #innodb_buffer_pool_size = 100M
    #innodb_log_file_size = 128M
innodb_log_files_in_group = 2
    #innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

'''
    mysqlconf += f"server-id = {data['id']}\n"
    mysqlconf += "#本节点的IP地址和端口，注意该端口是组内成员之间通信的端口，而不是MySQL对外提供服务的端口\n"
    mysqlconf += f"loose-group_replication_local_address = {data['ipaddr']}:{data['port']}\n"

    if select=="masters":
        pass
    elif select=="slaves":
        mysqlconf += "#种子节点的IP和端口号，新成员加入到集群的时候需要联系种子节点，启动集群的节点不使用下面该选项\n"
        mysqlconf += f"loose-group_replication_group_seeds = {getallipport()}\n"

    return mysqlconf

def mk_dockerfile(select="masters",id=0):
    return f'''
version: "3.1"

services:
    db:
        image: mysql:8.0.20
        hostname: {members[select][id]["hostname"]}
#    ports:
#        - "3306:3306"
#        - "33006:33006"
#        - "33060:33060"
        network_mode: "host"
        volumes:
            - "\$PWD/conf:/etc/mysql/conf.d"
            - "\$PWD/data:/var/lib/mysql"
            - "/etc/hosts:/etc/hosts"
        #command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
            MYSQL_ROOT_PASSWORD: {members["rootpassword"]}
'''

def mk_hosts():
    hosts = ""
    for m in members['masters']:
        hosts += f"{m['ipaddr']}\t{m['hostname']}\n"
    for s in members['slaves']:
        hosts += f"{s['ipaddr']}\t{s['hostname']}\n"
    return hosts

def mk_initsql(select="masters",id=0):
    sql = f'''
--- 这段所有机器都要加，是复制时候用的账号
--- mysql8.0 建立用户和角色，用户通过角色获得权限
set sql_log_bin=0;
create role 'syncusergroup';
grant replication slave, replication client on *.* to 'syncusergroup';
create user '{members['repuser']}'@'%' identified by '{members['repuser_pwd']}';
grant 'syncusergroup' to '{members['repuser']}'@'%';
create user '{members['repuser']}'@'127.0.0.1' identified by '{members['repuser_pwd']}';
grant 'syncusergroup' to '{members['repuser']}'@'127.0.0.1';
create user '{members['repuser']}'@'localhost' identified by '{members['repuser_pwd']}';
grant 'syncusergroup' to '{members['repuser']}'@'localhost';
set sql_log_bin=1;

'''
    if select=="masters":
        sql += f'''
--- 这段是在master上加，第一次用的时候加。
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
'''
    elif select=="slaves":
        sql += f'''
--- 这段是在所有slave上加，第一次用的时候加。
change master to master_user='{members['repuser']}',master_password='{members['repuser_pwd']}' for channel 'group_replication_recovery';
reset master;
start group_replication;
'''
    return sql

def mk_Readme(select="masters",id=0):
    return f'''
先将hosts里的文件添加到/etc/hosts下面可以使用命令
cat hosts >> /etc/hosts
上面命令避免重复执行，因为会重复添加

可以将{members[select][id]['hostname']}这个目录名字统一改为mysql，不改也可以    

最后执行 
docker-compose up -d
进行最终运行
'''

def mk_conf(conffile):
    conf='''
# 用于生成mysql8主从复制的配置文件
# 用于生成docker-compose文件

# 两种使用方法
# 第一种  设置zaiyiqi-enable（在一起）: True，outputroot起效
# 在无关电脑上运行，会依据members生成n份配置文件，将n份配置文件逐一拷贝到n台mysql服务器上，然后按提示运行
# 3台自己组成集群
# 第二种 设置zaiyiqi（在一起）: False
# 将此程序拷贝到3台mysql服务器上，运行时指定自己身份是master1还是slave1或2
# 程序自动配置，按提示运行，3台自己组成集群

# 是否将配置生成在1个统一目录下
zaiyiqi:
  enable: True
  outputroot: "output"

# master成员，只能有1个。成员id全局唯一
# linux下可以输入命令hostname查看hostname
masters:
  - hostname: "hostname1"
    ipaddr: "10.2.1.1"
    port: "33006"
    id: "66"
# slaves成员，有多个，一般为2个。成员id全局唯一
slaves:
  - hostname: "hostname2"
    ipaddr: "10.2.1.2"
    port: "33006"
    id: "67"
  - hostname: "hostname3"
    ipaddr: "10.2.1.3"
    port: "33006"
    id: "68"
  - hostname: "hostname4"
    ipaddr: "10.2.5.1"
    port: "33006"
    id: "69"

#数据库root密码
rootpassword: "rootpwd"

#数据同步时用的账号和密码
repuser: "repuser"
repuser_pwd: "repuser123456"  
'''
    f = open(conffile, "w")
    f.write(conf)
    f.close()

def mk(select="masters",id=0):
    outputdir = members['zaiyiqi']['outputroot']
    dirname=os.path.join(outputdir,select+str(id))
    dirname = os.path.join(outputdir, members[select][id]['hostname'])
    if not os.path.exists(dirname):
        os.makedirs(dirname)
    filename = os.path.join(dirname, "Readme.txt")
    f = open(filename, "w")
    f.write(mk_Readme(select,id))
    f.close()
    filename=os.path.join(dirname,"hosts")
    f = open(filename, "w")
    f.write(mk_hosts())
    f.close()
    filename = os.path.join(dirname, "docker-compose.yml")
    f = open(filename, "w")
    f.write(mk_dockerfile(select, id))
    f.close()

    mysqlconfdir = os.path.join(dirname, "conf")
    if not os.path.exists(mysqlconfdir):
        os.makedirs(mysqlconfdir)
    filename = os.path.join(mysqlconfdir, "my.cnf")
    f = open(filename, "w")
    f.write(mk_mysql_conf(select, id))
    f.close()

    mysqldatadir=os.path.join(dirname,"data")
    if not os.path.exists(mysqldatadir):
        os.makedirs(mysqldatadir)
    filename = os.path.join(mysqldatadir, "first.sql")
    f = open(filename, "w")
    f.write(mk_initsql(select, id))
    f.close()

def dorun():
    if members['zaiyiqi']['enable']:
        for i in range(0,len(members['masters'])):
            mk("masters",i)
        for i in range(0, len(members['slaves'])):
            mk("slaves", i)
        print(f"配置生成完毕,在{members['zaiyiqi']['outputroot']}目录下,先将该目录下几个子目录拷贝到每个主机上,然后可以按Readme文件提示进行操作")

runit()
# mk("masters",0)
# mk("slaves",0)
# mk("slaves",1)
